import json

press_tiexi_sql_list = [
    """
    with op_base as (
        SELECT 
          case when length(t.MATERIAL_NUMBER)>8 then substr(t.MATERIAL_NUMBER,1,7) else t.MATERIAL_NUMBER end as MAT_SNR,
          replace(Substr(t.MATERIAL_NUMBER,8,3) ,'-','')as AI,
          case when t.CONTROL_KEY='ZA03' AND substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_RELEV,
          case when substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_SERIAL,
          case when substr(t.ORDER_TYPE,3,2) in ('P2', 'P4') then 1 else 0 end as IS_COMMIS,
          case when substr(t.ORDER_TYPE,3,2) in ('P5')       then 1 else 0 end as IS_MAINT,
          case when t.CONTROL_KEY='ZA03'                     then 1 else 0 end as IS_HEADER,
          (select listagg(substr(COMPONENT_NUMBER,1,7),' ') within group (order by POSITION_NUMBER) FROM QQRPWO.V_MD_OPERATION_COMPONENT co WHERE co.OPERATION_ID=t.OPERATION_ID) as Components,
          (select listagg(replace(MATERIAL_NUMBER,'P-',''),' ') within group (order by PTR_SEQUENCE) FROM QQRPWO.V_MD_PRODUCTION_TOOL_RESOURCE co WHERE co.OPERATION_ID=t.OPERATION_ID) as Tools,
          t.uf_01 as MITARBEITER, t.uf_05 as MAT_FZG,  t.uf_06 as MAT_TYP,  t.uf_07 as MAT_ART, t.uf_10 as WERKST, t.*
          FROM QQRPWO.V_MD_OPERATION t),
    op_shift as (
            SELECT x.operation_id,
             x.STATUS_MNEMONIC, x.STATUS_COLOR, x.PHASE_MNEMONIC, x.PHASE_COLOR, 
             x.START_TS, x.END_TS, x.END_TS_DATE, x.END_TS_YEAR, x.END_TS_MONTH, x.END_TS_ISOYEAR, x.END_TS_ISOWEEK,
             x.Sum_DURATION/1000/60 as Sum_DURATION, 
             (SELECT Round(1/(Greatest(PIECE_TIME_VALUE,1)/1000/60),0) FROM QQRPWO.V_MD_WORKPLACE w WHERE w.WORKPLACE_ID=x.WORKPLACE_ID) as HUB_MAX,
            (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
              WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'BNZ' 
                AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as BNZ_ZEIT,            
            (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
              WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
                AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as HOS_Zeit,           
            (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
              WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
                AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.NUMBER_OF_STROKES end) as HOS_Huebe,
            to_char(s.ASSOCIATED_DAY_TS,'YYYY')    as JAHR,
            to_char(s.ASSOCIATED_DAY_TS,'YYYY/MM') as JAHRMONAT,
            to_char(s.ASSOCIATED_DAY_TS,'IYYY-IW') as JAHRWOCHE,
            cast(s.ASSOCIATED_DAY_TS as date)      as TAG,
            cast(s.START_TS as date)               as SCHICHT,
            case when x.operation_id<>LAG(x.operation_id,1) over(ORDER BY x.operation_id) then 1 else 0 end as op_changed,
            x.NUMBER_OF_STROKES AS HUB,  x.YIELD_QTY  AS GUTMENGE, x.SCRAP_QTY AS AUSSCHUSS,x.REWORK_QTY  AS NACHARBEIT
            FROM   ((( QQRPWO.V_SC_OPERATION_QUANTITY_SUM x 
            INNER JOIN QQRPWO.FR_MD_SHIFT s ON x.SHIFT_ID=s.ID)
            LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON_VEC vec ON x.MFR_VECTOR_ID=vec.id)
            LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON mfr ON mfr.ID=vec.REASON_ID_1)
            where x.WORKPLACE_ID =667412700
      and  to_char(ASSOCIATED_DAY_TS,'YYYYMMDD') BETWEEN to_char(sysdate,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD')
            and x.PHASE_MNEMONIC<>'45'), 
      main as (SELECT 
        Nvl((op_base.WP_TARGET_NAME),'---') as Line, JahrWoche as Year_Week,
        cast(min(op_shift.START_TS) as date) as STARTZEIT,
        cast(Max(op_shift.END_TS) as date) as Endzeit,
        case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_HUEBE,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit/60,null)),  1) else null end as Stundenleistung,
        case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_Zeit ,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null))*100, 1) else null end as Nutzgrad
    FROM op_shift INNER JOIN op_base ON op_shift.operation_id=op_base.operation_id WHERE 1=1 GROUP BY  Nvl((op_base.WP_TARGET_NAME),'---'), JahrWoche
    ORDER BY 1,2)
    SELECT m.STUNDENLEISTUNG FROM main m ORDER BY 1
    """,
    """
    with op_base as (
        SELECT 
          case when length(t.MATERIAL_NUMBER)>8 then substr(t.MATERIAL_NUMBER,1,7) else t.MATERIAL_NUMBER end as MAT_SNR,
          replace(Substr(t.MATERIAL_NUMBER,8,3) ,'-','')as AI,
          case when t.CONTROL_KEY='ZA03' AND substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_RELEV,
          case when substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_SERIAL,
          case when substr(t.ORDER_TYPE,3,2) in ('P2', 'P4') then 1 else 0 end as IS_COMMIS,
          case when substr(t.ORDER_TYPE,3,2) in ('P5')       then 1 else 0 end as IS_MAINT,
          case when t.CONTROL_KEY='ZA03'                     then 1 else 0 end as IS_HEADER,
          (select listagg(substr(COMPONENT_NUMBER,1,7),' ') within group (order by POSITION_NUMBER) FROM QQRPWO.V_MD_OPERATION_COMPONENT co WHERE co.OPERATION_ID=t.OPERATION_ID) as Components,
          (select listagg(replace(MATERIAL_NUMBER,'P-',''),' ') within group (order by PTR_SEQUENCE) FROM QQRPWO.V_MD_PRODUCTION_TOOL_RESOURCE co WHERE co.OPERATION_ID=t.OPERATION_ID) as Tools,
          t.uf_01 as MITARBEITER, t.uf_05 as MAT_FZG,  t.uf_06 as MAT_TYP,  t.uf_07 as MAT_ART, t.uf_10 as WERKST, t.*
          FROM QQRPWO.V_MD_OPERATION t),
    op_shift as (
            SELECT x.operation_id,
             x.STATUS_MNEMONIC, x.STATUS_COLOR, x.PHASE_MNEMONIC, x.PHASE_COLOR, 
             x.START_TS, x.END_TS, x.END_TS_DATE, x.END_TS_YEAR, x.END_TS_MONTH, x.END_TS_ISOYEAR, x.END_TS_ISOWEEK,
             x.Sum_DURATION/1000/60 as Sum_DURATION, 
             (SELECT Round(1/(Greatest(PIECE_TIME_VALUE,1)/1000/60),0) FROM QQRPWO.V_MD_WORKPLACE w WHERE w.WORKPLACE_ID=x.WORKPLACE_ID) as HUB_MAX,
            (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
              WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'BNZ' 
                AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as BNZ_ZEIT,            
            (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
              WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
                AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as HOS_Zeit,           
            (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
              WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
                AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.NUMBER_OF_STROKES end) as HOS_Huebe,
            to_char(s.ASSOCIATED_DAY_TS,'YYYY')    as JAHR,
            to_char(s.ASSOCIATED_DAY_TS,'YYYY/MM') as JAHRMONAT,
            to_char(s.ASSOCIATED_DAY_TS,'IYYY-IW') as JAHRWOCHE,
            cast(s.ASSOCIATED_DAY_TS as date)      as TAG,
            cast(s.START_TS as date)               as SCHICHT,
            case when x.operation_id<>LAG(x.operation_id,1) over(ORDER BY x.operation_id) then 1 else 0 end as op_changed,
            x.NUMBER_OF_STROKES AS HUB,  x.YIELD_QTY  AS GUTMENGE, x.SCRAP_QTY AS AUSSCHUSS,x.REWORK_QTY  AS NACHARBEIT
            FROM   ((( QQRPWO.V_SC_OPERATION_QUANTITY_SUM x 
            INNER JOIN QQRPWO.FR_MD_SHIFT s ON x.SHIFT_ID=s.ID)
            LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON_VEC vec ON x.MFR_VECTOR_ID=vec.id)
            LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON mfr ON mfr.ID=vec.REASON_ID_1)
            where x.WORKPLACE_ID =667412700
         and  to_char(ASSOCIATED_DAY_TS,'YYYYMMDD') BETWEEN to_char(sysdate,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD')
            and x.PHASE_MNEMONIC<>'45'), 
      main as (SELECT 
        Nvl((op_base.WP_TARGET_NAME),'---') as Line, JahrWoche as Year_Week,
        cast(min(op_shift.START_TS) as date) as STARTZEIT,
        cast(Max(op_shift.END_TS) as date) as Endzeit,
        case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_HUEBE,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit/60,null)),  1) else null end as Stundenleistung,
        case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_Zeit ,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null))*100, 1) else null end as Nutzgrad
    FROM op_shift INNER JOIN op_base ON op_shift.operation_id=op_base.operation_id WHERE 1=1 GROUP BY  Nvl((op_base.WP_TARGET_NAME),'---'), JahrWoche
    ORDER BY 1,2)
    SELECT m.NUTZGRAD FROM main m ORDER BY 1
    """,
    """
with op_base as (
    SELECT 
      case when length(t.MATERIAL_NUMBER)>8 then substr(t.MATERIAL_NUMBER,1,7) else t.MATERIAL_NUMBER end as MAT_SNR,
      replace(Substr(t.MATERIAL_NUMBER,8,3) ,'-','')as AI,
      case when t.CONTROL_KEY='ZA03' AND substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_RELEV,
      case when substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_SERIAL,
      case when substr(t.ORDER_TYPE,3,2) in ('P2', 'P4') then 1 else 0 end as IS_COMMIS,
      case when substr(t.ORDER_TYPE,3,2) in ('P5')       then 1 else 0 end as IS_MAINT,
      case when t.CONTROL_KEY='ZA03'                     then 1 else 0 end as IS_HEADER,
      (select listagg(substr(COMPONENT_NUMBER,1,7),' ') within group (order by POSITION_NUMBER) FROM QQRPWO.V_MD_OPERATION_COMPONENT co WHERE co.OPERATION_ID=t.OPERATION_ID) as Components,
      (select listagg(replace(MATERIAL_NUMBER,'P-',''),' ') within group (order by PTR_SEQUENCE) FROM QQRPWO.V_MD_PRODUCTION_TOOL_RESOURCE co WHERE co.OPERATION_ID=t.OPERATION_ID) as Tools,
      t.uf_01 as MITARBEITER, t.uf_05 as MAT_FZG,  t.uf_06 as MAT_TYP,  t.uf_07 as MAT_ART, t.uf_10 as WERKST, t.*
      FROM QQRPWO.V_MD_OPERATION t),
op_shift as (
        SELECT x.operation_id,
         x.STATUS_MNEMONIC, x.STATUS_COLOR, x.PHASE_MNEMONIC, x.PHASE_COLOR, 
         x.START_TS, x.END_TS, x.END_TS_DATE, x.END_TS_YEAR, x.END_TS_MONTH, x.END_TS_ISOYEAR, x.END_TS_ISOWEEK,
         x.Sum_DURATION/1000/60 as Sum_DURATION, 
         (SELECT Round(1/(Greatest(PIECE_TIME_VALUE,1)/1000/60),0) FROM QQRPWO.V_MD_WORKPLACE w WHERE w.WORKPLACE_ID=x.WORKPLACE_ID) as HUB_MAX,
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'BNZ' 
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as BNZ_ZEIT,            
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as HOS_Zeit,           
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.NUMBER_OF_STROKES end) as HOS_Huebe,
        to_char(s.ASSOCIATED_DAY_TS,'YYYY')    as JAHR,
        to_char(s.ASSOCIATED_DAY_TS,'YYYY/MM') as JAHRMONAT,
        to_char(s.ASSOCIATED_DAY_TS,'IYYY-IW') as JAHRWOCHE,
        cast(s.ASSOCIATED_DAY_TS as date)      as TAG,
        cast(s.START_TS as date)               as SCHICHT,
        case when x.operation_id<>LAG(x.operation_id,1) over(ORDER BY x.operation_id) then 1 else 0 end as op_changed,
        x.NUMBER_OF_STROKES AS HUB,  x.YIELD_QTY  AS GUTMENGE, x.SCRAP_QTY AS AUSSCHUSS,x.REWORK_QTY  AS NACHARBEIT
        FROM   ((( QQRPWO.V_SC_OPERATION_QUANTITY_SUM x 
        INNER JOIN QQRPWO.FR_MD_SHIFT s ON x.SHIFT_ID=s.ID)
        LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON_VEC vec ON x.MFR_VECTOR_ID=vec.id)
        LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON mfr ON mfr.ID=vec.REASON_ID_1)
        where x.WORKPLACE_ID =667412701
  and  to_char(ASSOCIATED_DAY_TS,'YYYYMMDD') BETWEEN to_char(sysdate,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD')
        and x.PHASE_MNEMONIC<>'45'), 
  main as (SELECT 
    Nvl((op_base.WP_TARGET_NAME),'---') as Line, JahrWoche as Year_Week,
    cast(min(op_shift.START_TS) as date) as STARTZEIT,
    cast(Max(op_shift.END_TS) as date) as Endzeit,
    case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_HUEBE,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit/60,null)),  1) else null end as Stundenleistung,
    case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_Zeit ,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null))*100, 1) else null end as Nutzgrad
FROM op_shift INNER JOIN op_base ON op_shift.operation_id=op_base.operation_id WHERE 1=1 GROUP BY  Nvl((op_base.WP_TARGET_NAME),'---'), JahrWoche
ORDER BY 1,2)
SELECT m.STUNDENLEISTUNG FROM main m ORDER BY 1
""",
    """
with op_base as (
    SELECT 
      case when length(t.MATERIAL_NUMBER)>8 then substr(t.MATERIAL_NUMBER,1,7) else t.MATERIAL_NUMBER end as MAT_SNR,
      replace(Substr(t.MATERIAL_NUMBER,8,3) ,'-','')as AI,
      case when t.CONTROL_KEY='ZA03' AND substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_RELEV,
      case when substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_SERIAL,
      case when substr(t.ORDER_TYPE,3,2) in ('P2', 'P4') then 1 else 0 end as IS_COMMIS,
      case when substr(t.ORDER_TYPE,3,2) in ('P5')       then 1 else 0 end as IS_MAINT,
      case when t.CONTROL_KEY='ZA03'                     then 1 else 0 end as IS_HEADER,
      (select listagg(substr(COMPONENT_NUMBER,1,7),' ') within group (order by POSITION_NUMBER) FROM QQRPWO.V_MD_OPERATION_COMPONENT co WHERE co.OPERATION_ID=t.OPERATION_ID) as Components,
      (select listagg(replace(MATERIAL_NUMBER,'P-',''),' ') within group (order by PTR_SEQUENCE) FROM QQRPWO.V_MD_PRODUCTION_TOOL_RESOURCE co WHERE co.OPERATION_ID=t.OPERATION_ID) as Tools,
      t.uf_01 as MITARBEITER, t.uf_05 as MAT_FZG,  t.uf_06 as MAT_TYP,  t.uf_07 as MAT_ART, t.uf_10 as WERKST, t.*
      FROM QQRPWO.V_MD_OPERATION t),
op_shift as (
        SELECT x.operation_id,
         x.STATUS_MNEMONIC, x.STATUS_COLOR, x.PHASE_MNEMONIC, x.PHASE_COLOR, 
         x.START_TS, x.END_TS, x.END_TS_DATE, x.END_TS_YEAR, x.END_TS_MONTH, x.END_TS_ISOYEAR, x.END_TS_ISOWEEK,
         x.Sum_DURATION/1000/60 as Sum_DURATION, 
         (SELECT Round(1/(Greatest(PIECE_TIME_VALUE,1)/1000/60),0) FROM QQRPWO.V_MD_WORKPLACE w WHERE w.WORKPLACE_ID=x.WORKPLACE_ID) as HUB_MAX,
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'BNZ' 
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as BNZ_ZEIT,            
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as HOS_Zeit,           
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.NUMBER_OF_STROKES end) as HOS_Huebe,
        to_char(s.ASSOCIATED_DAY_TS,'YYYY')    as JAHR,
        to_char(s.ASSOCIATED_DAY_TS,'YYYY/MM') as JAHRMONAT,
        to_char(s.ASSOCIATED_DAY_TS,'IYYY-IW') as JAHRWOCHE,
        cast(s.ASSOCIATED_DAY_TS as date)      as TAG,
        cast(s.START_TS as date)               as SCHICHT,
        case when x.operation_id<>LAG(x.operation_id,1) over(ORDER BY x.operation_id) then 1 else 0 end as op_changed,
        x.NUMBER_OF_STROKES AS HUB,  x.YIELD_QTY  AS GUTMENGE, x.SCRAP_QTY AS AUSSCHUSS,x.REWORK_QTY  AS NACHARBEIT
        FROM   ((( QQRPWO.V_SC_OPERATION_QUANTITY_SUM x 
        INNER JOIN QQRPWO.FR_MD_SHIFT s ON x.SHIFT_ID=s.ID)
        LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON_VEC vec ON x.MFR_VECTOR_ID=vec.id)
        LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON mfr ON mfr.ID=vec.REASON_ID_1)
        where x.WORKPLACE_ID =667412701
     and  to_char(ASSOCIATED_DAY_TS,'YYYYMMDD') BETWEEN to_char(sysdate,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD')
        and x.PHASE_MNEMONIC<>'45'), 
  main as (SELECT 
    Nvl((op_base.WP_TARGET_NAME),'---') as Line, JahrWoche as Year_Week,
    cast(min(op_shift.START_TS) as date) as STARTZEIT,
    cast(Max(op_shift.END_TS) as date) as Endzeit,
    case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_HUEBE,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit/60,null)),  1) else null end as Stundenleistung,
    case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_Zeit ,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null))*100, 1) else null end as Nutzgrad
FROM op_shift INNER JOIN op_base ON op_shift.operation_id=op_base.operation_id WHERE 1=1 GROUP BY  Nvl((op_base.WP_TARGET_NAME),'---'), JahrWoche
ORDER BY 1,2)
SELECT m.NUTZGRAD FROM main m ORDER BY 1
""",
    """
with op_base as (
    SELECT 
      case when length(t.MATERIAL_NUMBER)>8 then substr(t.MATERIAL_NUMBER,1,7) else t.MATERIAL_NUMBER end as MAT_SNR,
      replace(Substr(t.MATERIAL_NUMBER,8,3) ,'-','')as AI,
      case when t.CONTROL_KEY='ZA03' AND substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_RELEV,
      case when substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_SERIAL,
      case when substr(t.ORDER_TYPE,3,2) in ('P2', 'P4') then 1 else 0 end as IS_COMMIS,
      case when substr(t.ORDER_TYPE,3,2) in ('P5')       then 1 else 0 end as IS_MAINT,
      case when t.CONTROL_KEY='ZA03'                     then 1 else 0 end as IS_HEADER,
      (select listagg(substr(COMPONENT_NUMBER,1,7),' ') within group (order by POSITION_NUMBER) FROM QQRPWO.V_MD_OPERATION_COMPONENT co WHERE co.OPERATION_ID=t.OPERATION_ID) as Components,
      (select listagg(replace(MATERIAL_NUMBER,'P-',''),' ') within group (order by PTR_SEQUENCE) FROM QQRPWO.V_MD_PRODUCTION_TOOL_RESOURCE co WHERE co.OPERATION_ID=t.OPERATION_ID) as Tools,
      t.uf_01 as MITARBEITER, t.uf_05 as MAT_FZG,  t.uf_06 as MAT_TYP,  t.uf_07 as MAT_ART, t.uf_10 as WERKST, t.*
      FROM QQRPWO.V_MD_OPERATION t),
op_shift as (
        SELECT x.operation_id,
         x.STATUS_MNEMONIC, x.STATUS_COLOR, x.PHASE_MNEMONIC, x.PHASE_COLOR, 
         x.START_TS, x.END_TS, x.END_TS_DATE, x.END_TS_YEAR, x.END_TS_MONTH, x.END_TS_ISOYEAR, x.END_TS_ISOWEEK,
         x.Sum_DURATION/1000/60 as Sum_DURATION, 
         (SELECT Round(1/(Greatest(PIECE_TIME_VALUE,1)/1000/60),0) FROM QQRPWO.V_MD_WORKPLACE w WHERE w.WORKPLACE_ID=x.WORKPLACE_ID) as HUB_MAX,
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'BNZ' 
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as BNZ_ZEIT,            
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as HOS_Zeit,           
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.NUMBER_OF_STROKES end) as HOS_Huebe,
        to_char(s.ASSOCIATED_DAY_TS,'YYYY')    as JAHR,
        to_char(s.ASSOCIATED_DAY_TS,'YYYY/MM') as JAHRMONAT,
        to_char(s.ASSOCIATED_DAY_TS,'IYYY-IW') as JAHRWOCHE,
        cast(s.ASSOCIATED_DAY_TS as date)      as TAG,
        cast(s.START_TS as date)               as SCHICHT,
        case when x.operation_id<>LAG(x.operation_id,1) over(ORDER BY x.operation_id) then 1 else 0 end as op_changed,
        x.NUMBER_OF_STROKES AS HUB,  x.YIELD_QTY  AS GUTMENGE, x.SCRAP_QTY AS AUSSCHUSS,x.REWORK_QTY  AS NACHARBEIT
        FROM   ((( QQRPWO.V_SC_OPERATION_QUANTITY_SUM x 
        INNER JOIN QQRPWO.FR_MD_SHIFT s ON x.SHIFT_ID=s.ID)
        LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON_VEC vec ON x.MFR_VECTOR_ID=vec.id)
        LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON mfr ON mfr.ID=vec.REASON_ID_1)
        where x.WORKPLACE_ID =667412702 --- Filter Arbeitsplatz
      --and  ASSOCIATED_DAY_TS BETWEEN TO_TIMESTAMP (sysdate ,'YYYY-MM-DD hh24:mi:ss.ff') AND TO_TIMESTAMP (sysdate ,'YYYY-MM-DD hh24:mi:ss.ff')
  and  to_char(ASSOCIATED_DAY_TS,'YYYYMMDD') BETWEEN to_char(sysdate,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD')
        and x.PHASE_MNEMONIC<>'45'), 
  main as (SELECT 
    Nvl((op_base.WP_TARGET_NAME),'---') as Line, JahrWoche as Year_Week,
    cast(min(op_shift.START_TS) as date) as STARTZEIT,
    cast(Max(op_shift.END_TS) as date) as Endzeit,
    case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_HUEBE,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit/60,null)),  1) else null end as Stundenleistung,
    case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_Zeit ,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null))*100, 1) else null end as Nutzgrad
FROM op_shift INNER JOIN op_base ON op_shift.operation_id=op_base.operation_id WHERE 1=1 GROUP BY  Nvl((op_base.WP_TARGET_NAME),'---'), JahrWoche
ORDER BY 1,2)
SELECT m.STUNDENLEISTUNG FROM main m ORDER BY 1
""",
    """
with op_base as (
    SELECT 
      case when length(t.MATERIAL_NUMBER)>8 then substr(t.MATERIAL_NUMBER,1,7) else t.MATERIAL_NUMBER end as MAT_SNR,
      replace(Substr(t.MATERIAL_NUMBER,8,3) ,'-','')as AI,
      case when t.CONTROL_KEY='ZA03' AND substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_RELEV,
      case when substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_SERIAL,
      case when substr(t.ORDER_TYPE,3,2) in ('P2', 'P4') then 1 else 0 end as IS_COMMIS,
      case when substr(t.ORDER_TYPE,3,2) in ('P5')       then 1 else 0 end as IS_MAINT, 
      case when t.CONTROL_KEY='ZA03'                     then 1 else 0 end as IS_HEADER,   
      (select listagg(substr(COMPONENT_NUMBER,1,7),' ') within group (order by POSITION_NUMBER) FROM QQRPWO.V_MD_OPERATION_COMPONENT co WHERE co.OPERATION_ID=t.OPERATION_ID) as Components,
      (select listagg(replace(MATERIAL_NUMBER,'P-',''),' ') within group (order by PTR_SEQUENCE) FROM QQRPWO.V_MD_PRODUCTION_TOOL_RESOURCE co WHERE co.OPERATION_ID=t.OPERATION_ID) as Tools,
      t.uf_01 as MITARBEITER, t.uf_05 as MAT_FZG,  t.uf_06 as MAT_TYP,  t.uf_07 as MAT_ART, t.uf_10 as WERKST, t.*
      FROM QQRPWO.V_MD_OPERATION t),
op_shift as (
        SELECT x.operation_id,
         x.STATUS_MNEMONIC, x.STATUS_COLOR, x.PHASE_MNEMONIC, x.PHASE_COLOR, 
         x.START_TS, x.END_TS, x.END_TS_DATE, x.END_TS_YEAR, x.END_TS_MONTH, x.END_TS_ISOYEAR, x.END_TS_ISOWEEK,
         x.Sum_DURATION/1000/60 as Sum_DURATION, 
         (SELECT Round(1/(Greatest(PIECE_TIME_VALUE,1)/1000/60),0) FROM QQRPWO.V_MD_WORKPLACE w WHERE w.WORKPLACE_ID=x.WORKPLACE_ID) as HUB_MAX,
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'BNZ' 
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as BNZ_ZEIT,            
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as HOS_Zeit,           
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.NUMBER_OF_STROKES end) as HOS_Huebe,
        to_char(s.ASSOCIATED_DAY_TS,'YYYY')    as JAHR,
        to_char(s.ASSOCIATED_DAY_TS,'YYYY/MM') as JAHRMONAT,
        to_char(s.ASSOCIATED_DAY_TS,'IYYY-IW') as JAHRWOCHE,
        cast(s.ASSOCIATED_DAY_TS as date)      as TAG,
        cast(s.START_TS as date)               as SCHICHT,
        case when x.operation_id<>LAG(x.operation_id,1) over(ORDER BY x.operation_id) then 1 else 0 end as op_changed,
        x.NUMBER_OF_STROKES AS HUB,  x.YIELD_QTY  AS GUTMENGE, x.SCRAP_QTY AS AUSSCHUSS,x.REWORK_QTY  AS NACHARBEIT
        FROM   ((( QQRPWO.V_SC_OPERATION_QUANTITY_SUM x 
        INNER JOIN QQRPWO.FR_MD_SHIFT s ON x.SHIFT_ID=s.ID)
        LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON_VEC vec ON x.MFR_VECTOR_ID=vec.id)
        LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON mfr ON mfr.ID=vec.REASON_ID_1)
        where x.WORKPLACE_ID =667412702
     and  to_char(ASSOCIATED_DAY_TS,'YYYYMMDD') BETWEEN to_char(sysdate,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD')
        and x.PHASE_MNEMONIC<>'45'), 
  main as (SELECT 
    Nvl((op_base.WP_TARGET_NAME),'---') as Line, JahrWoche as Year_Week,
    cast(min(op_shift.START_TS) as date) as STARTZEIT,
    cast(Max(op_shift.END_TS) as date) as Endzeit,
    case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_HUEBE,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit/60,null)),  1) else null end as Stundenleistung,
    case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_Zeit ,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null))*100, 1) else null end as Nutzgrad
FROM op_shift INNER JOIN op_base ON op_shift.operation_id=op_base.operation_id WHERE 1=1 GROUP BY  Nvl((op_base.WP_TARGET_NAME),'---'), JahrWoche
ORDER BY 1,2)
SELECT m.NUTZGRAD FROM main m ORDER BY 1
"""
]
press_dadong_sql_list = [
    """
        with op_base as (
        SELECT 
          case when length(t.MATERIAL_NUMBER)>8 then substr(t.MATERIAL_NUMBER,1,7) else t.MATERIAL_NUMBER end as MAT_SNR,
          replace(Substr(t.MATERIAL_NUMBER,8,3) ,'-','')as AI,
          case when t.CONTROL_KEY='ZA03' AND substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_RELEV,
          case when substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_SERIAL,
          case when substr(t.ORDER_TYPE,3,2) in ('P2', 'P4') then 1 else 0 end as IS_COMMIS,
          case when substr(t.ORDER_TYPE,3,2) in ('P5')       then 1 else 0 end as IS_MAINT, 
          case when t.CONTROL_KEY='ZA03'                     then 1 else 0 end as IS_HEADER,
          (select listagg(substr(COMPONENT_NUMBER,1,7),' ') within group (order by POSITION_NUMBER) FROM QQRPWO.V_MD_OPERATION_COMPONENT co WHERE co.OPERATION_ID=t.OPERATION_ID) as Components,
          (select listagg(replace(MATERIAL_NUMBER,'P-',''),' ') within group (order by PTR_SEQUENCE) FROM QQRPWO.V_MD_PRODUCTION_TOOL_RESOURCE co WHERE co.OPERATION_ID=t.OPERATION_ID) as Tools,
          t.uf_01 as MITARBEITER, t.uf_05 as MAT_FZG,  t.uf_06 as MAT_TYP,  t.uf_07 as MAT_ART, t.uf_10 as WERKST, t.*
          FROM QQRPWO.V_MD_OPERATION t),
    op_shift as (
            SELECT x.operation_id,
             x.STATUS_MNEMONIC, x.STATUS_COLOR, x.PHASE_MNEMONIC, x.PHASE_COLOR, 
             x.START_TS, x.END_TS, x.END_TS_DATE, x.END_TS_YEAR, x.END_TS_MONTH, x.END_TS_ISOYEAR, x.END_TS_ISOWEEK,
             x.Sum_DURATION/1000/60 as Sum_DURATION, 
             (SELECT Round(1/(Greatest(PIECE_TIME_VALUE,1)/1000/60),0) FROM QQRPWO.V_MD_WORKPLACE w WHERE w.WORKPLACE_ID=x.WORKPLACE_ID) as HUB_MAX,
            (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
              WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'BNZ' 
                AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as BNZ_ZEIT,            
            (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
              WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
                AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as HOS_Zeit,           
            (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
              WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
                AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.NUMBER_OF_STROKES end) as HOS_Huebe,
            to_char(s.ASSOCIATED_DAY_TS,'YYYY')    as JAHR,
            to_char(s.ASSOCIATED_DAY_TS,'YYYY/MM') as JAHRMONAT,
            to_char(s.ASSOCIATED_DAY_TS,'IYYY-IW') as JAHRWOCHE,
            cast(s.ASSOCIATED_DAY_TS as date)      as TAG,
            cast(s.START_TS as date)               as SCHICHT,
            case when x.operation_id<>LAG(x.operation_id,1) over(ORDER BY x.operation_id) then 1 else 0 end as op_changed,
            x.NUMBER_OF_STROKES AS HUB,  x.YIELD_QTY  AS GUTMENGE, x.SCRAP_QTY AS AUSSCHUSS,x.REWORK_QTY  AS NACHARBEIT
            FROM   ((( QQRPWO.V_SC_OPERATION_QUANTITY_SUM x 
            INNER JOIN QQRPWO.FR_MD_SHIFT s ON x.SHIFT_ID=s.ID)
            LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON_VEC vec ON x.MFR_VECTOR_ID=vec.id)
            LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON mfr ON mfr.ID=vec.REASON_ID_1)
            where x.WORKPLACE_ID =667302551
      and  to_char(ASSOCIATED_DAY_TS,'YYYYMMDD') BETWEEN to_char(sysdate,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD')
            and x.PHASE_MNEMONIC<>'45'), 
      main as (SELECT 
        Nvl((op_base.WP_TARGET_NAME),'---') as Line, JahrWoche as Year_Week,
        cast(min(op_shift.START_TS) as date) as STARTZEIT,
        cast(Max(op_shift.END_TS) as date) as Endzeit,
        case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_HUEBE,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit/60,null)),  1) else null end as Stundenleistung,
        case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_Zeit ,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null))*100, 1) else null end as Nutzgrad
    FROM op_shift INNER JOIN op_base ON op_shift.operation_id=op_base.operation_id WHERE 1=1 GROUP BY  Nvl((op_base.WP_TARGET_NAME),'---'), JahrWoche
    ORDER BY 1,2)
    SELECT m.STUNDENLEISTUNG FROM main m ORDER BY 1
        """,
    """
    with op_base as ( -- Sub-Mapping aller nicht-BWM-konformen Inhalte
    SELECT 
      case when length(t.MATERIAL_NUMBER)>8 then substr(t.MATERIAL_NUMBER,1,7) else t.MATERIAL_NUMBER end as MAT_SNR,
      replace(Substr(t.MATERIAL_NUMBER,8,3) ,'-','')as AI,
      case when t.CONTROL_KEY='ZA03' AND substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_RELEV, -- Hauptteil Serienauftrag
      case when substr(t.ORDER_TYPE,3,2) in ('P1', 'P3') then 1 else 0 end as IS_SERIAL, -- Serienauftrag
      case when substr(t.ORDER_TYPE,3,2) in ('P2', 'P4') then 1 else 0 end as IS_COMMIS, -- Einarbeitsauftrag
      case when substr(t.ORDER_TYPE,3,2) in ('P5')       then 1 else 0 end as IS_MAINT,  -- Wartungsauftrag 
      case when t.CONTROL_KEY='ZA03'                     then 1 else 0 end as IS_HEADER, -- Hauptteil     
      (select listagg(substr(COMPONENT_NUMBER,1,7),' ') within group (order by POSITION_NUMBER) FROM QQRPWO.V_MD_OPERATION_COMPONENT co WHERE co.OPERATION_ID=t.OPERATION_ID) as Components,
      (select listagg(replace(MATERIAL_NUMBER,'P-',''),' ') within group (order by PTR_SEQUENCE) FROM QQRPWO.V_MD_PRODUCTION_TOOL_RESOURCE co WHERE co.OPERATION_ID=t.OPERATION_ID) as Tools,
      t.uf_01 as MITARBEITER, t.uf_05 as MAT_FZG,  t.uf_06 as MAT_TYP,  t.uf_07 as MAT_ART, t.uf_10 as WERKST, t.*
      FROM QQRPWO.V_MD_OPERATION t),
op_shift as (        -- Inneres SELECT zur Summieren der Zeiten pro APL, AVO, Schicht, Zustand
        SELECT x.operation_id,
         x.STATUS_MNEMONIC, x.STATUS_COLOR, x.PHASE_MNEMONIC, x.PHASE_COLOR, 
         x.START_TS, x.END_TS, x.END_TS_DATE, x.END_TS_YEAR, x.END_TS_MONTH, x.END_TS_ISOYEAR, x.END_TS_ISOWEEK,
         x.Sum_DURATION/1000/60 as Sum_DURATION, 
         (SELECT Round(1/(Greatest(PIECE_TIME_VALUE,1)/1000/60),0) FROM QQRPWO.V_MD_WORKPLACE w WHERE w.WORKPLACE_ID=x.WORKPLACE_ID) as HUB_MAX,
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'BNZ' 
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as BNZ_ZEIT,            
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.Sum_DURATION/1000/60 end) as HOS_Zeit,           
        (case (select count(*) from QQRPWO.V_OPERATION_TIMEBASE TB 
          WHERE TB.PHASE_CODE = x.phase_code AND TB.STATUS_CODE = x.status_code AND TB.TIME_BASE_MNEMONIC = 'HOS'
            AND Nvl(TB.MFR_CODE,0) IN (0, mfr.code)) when 0 then null else x.NUMBER_OF_STROKES end) as HOS_Huebe,
        to_char(s.ASSOCIATED_DAY_TS,'YYYY')    as JAHR,
        to_char(s.ASSOCIATED_DAY_TS,'YYYY/MM') as JAHRMONAT,
        to_char(s.ASSOCIATED_DAY_TS,'IYYY-IW') as JAHRWOCHE,
        cast(s.ASSOCIATED_DAY_TS as date)      as TAG,
        cast(s.START_TS as date)               as SCHICHT,
        case when x.operation_id<>LAG(x.operation_id,1) over(ORDER BY x.operation_id) then 1 else 0 end as op_changed,
        x.NUMBER_OF_STROKES AS HUB,  x.YIELD_QTY  AS GUTMENGE, x.SCRAP_QTY AS AUSSCHUSS,x.REWORK_QTY  AS NACHARBEIT
        FROM   ((( QQRPWO.V_SC_OPERATION_QUANTITY_SUM x 
        INNER JOIN QQRPWO.FR_MD_SHIFT s ON x.SHIFT_ID=s.ID)
        LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON_VEC vec ON x.MFR_VECTOR_ID=vec.id)
        LEFT JOIN QQRPWO.FR_MD_MALFUNCTION_REASON mfr ON mfr.ID=vec.REASON_ID_1)
        where x.WORKPLACE_ID =667302551 --- Filter Arbeitsplatz
      --and  ASSOCIATED_DAY_TS BETWEEN TO_TIMESTAMP (sysdate ,'YYYY-MM-DD hh24:mi:ss.ff') AND TO_TIMESTAMP (sysdate ,'YYYY-MM-DD hh24:mi:ss.ff')
     and  to_char(ASSOCIATED_DAY_TS,'YYYYMMDD') BETWEEN to_char(sysdate,'YYYYMMDD') AND to_char(sysdate,'YYYYMMDD')
        and x.PHASE_MNEMONIC<>'45'), 
  main as (SELECT 
    Nvl((op_base.WP_TARGET_NAME),'---') as Line, JahrWoche as Year_Week,
    cast(min(op_shift.START_TS) as date) as STARTZEIT,
    cast(Max(op_shift.END_TS) as date) as Endzeit,
    case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_HUEBE,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit/60,null)),  1) else null end as Stundenleistung,
    case when Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null)) > 0 then Round(Sum(decode(IS_RELEV,1,op_shift.HOS_Zeit ,null)) / Sum(decode(IS_RELEV,1,op_shift.BNZ_Zeit,null))*100, 1) else null end as Nutzgrad
FROM op_shift INNER JOIN op_base ON op_shift.operation_id=op_base.operation_id WHERE 1=1 GROUP BY  Nvl((op_base.WP_TARGET_NAME),'---'), JahrWoche
ORDER BY 1,2)
SELECT m.NUTZGRAD FROM main m ORDER BY 1
    """
]
filename = "./storage/data/pressInputFile.json"


def get_press_shop_data(bind_type):
    from app import db
    from flask import current_app
    global press_tiexi_sql_list
    global press_dadong_sql_list
    if bind_type == 'tx':
        db_bind_name = 'TIEXI_PRESS_SHOP'
        sql_list = press_tiexi_sql_list
        field_name_list = ['gp1930_current_sph', 'gp1930_current_uptime', 'gp1960_current_sph', 'gp1960_current_uptime',
                           'gp1961_current_sph', 'gp1961_current_uptime']
    else:
        db_bind_name = 'DADONG_PRESS_SHOP'
        sql_list = press_dadong_sql_list
        field_name_list = ['gp1965_current_sph', 'gp1965_current_uptime']
    db_engine = db.get_engine(current_app, bind=db_bind_name)
    idx = 0
    press_data_dict = {}
    for sql in sql_list:
        result_proxy = db_engine.execute(sql)
        row = result_proxy.fetchone()
        field_name = field_name_list[idx]
        if row is not None:
            press_data_dict[field_name] = str(row[0])
        else:
            press_data_dict[field_name] = '0'
        idx += 1
    return press_data_dict


def write_json(input_value):
    with open(filename, "w+") as file:
        json.dump(input_value, file)
        file.close()


def read_json():
    with open(filename) as file:
        input_value = json.load(file)
        file.close()
        return input_value
